/****** Object:  UserDefinedFunction [dbo].[STR_SIMILARITY]    Script Date: 2016/9/6 10:15:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[STR_SIMILARITY]
(
    @str1 VARCHAR(8000),  
    @str2 VARCHAR(8000)
)
RETURNS DECIMAL(5,4)
AS
BEGIN
	IF @str1=@str2
		RETURN 1;

	DECLARE @i   INT,					-- 最长字符串长度
            @j   INT,					-- 最短字符串长度
            @m   DECIMAL(9,4),			-- 匹配的字符串数目
			@t   DECIMAL(9,4),			-- 字符串位置差数目
			@k   INT,@l INT,			-- 临时变量
			@c   VARCHAR(2),			-- 临时字符
            @ret DECIMAL(5,4)
	DECLARE @index TABLE(idx INT, dis INT)	-- 数据表

	SET @i = Len(@str1)
    SET @j = Len(@str2)
    SET @m = 0
	SET @k = 1
	SET @l = 0
	SET @t= 0
  
    IF( @j = 0 OR @i = 0)  
		RETURN 0  

	WHILE @k<=@i
	BEGIN
		SET @c=SUBSTRING(@str1,@k,1)
		SET @l=CHARINDEX(@c,@str2);

		-- 需要考虑重复字符
		WHILE EXISTS (SELECT idx FROM @index WHERE idx=@l)
		BEGIN
			SET @l=CHARINDEX(@c,@str2,@l+1);
			IF @l<1
				BREAK
		END

		IF @l>0
		BEGIN
			INSERT @index VALUES(@l,@l-@k)
			SET @m=@m+1
		END
			
		SET @k = @k+1
	END;
  
    
	-- 如果再严谨一点，可以遍历所有同距离的字符集，并按权重计算出相似度结果

	-- WITH temp AS(SELECT COUNT(0) AS cnt FROM @index GROUP BY dis)
	-- SELECT @t=MAX(cnt) FROM temp;

	DECLARE tbl CURSOR FOR
	SELECT COUNT(0) AS cnt,dis FROM @index GROUP BY dis ORDER BY cnt DESC

	DECLARE @cnt INT,@dis INT,@max DECIMAL(9,5)
	IF @i>@j
		SET @max=@i
	ELSE
		SET @max=@j

	OPEN tbl
		FETCH NEXT FROM tbl INTO @cnt,@dis
		WHILE @@FETCH_STATUS=0
		BEGIN
			SET @t = @t + @cnt*(1-ABS(@dis)/@max)/@m
			FETCH NEXT FROM tbl INTO @cnt,@dis
		END
	CLOSE tbl
	DEALLOCATE tbl


	IF @m>1
		SET @ret = (@m/@i+@m/@j+@t)/3.0
	ELSE
		SET @ret = (@m/@i+@m/@j)/2.0
    RETURN @ret 
END



GO